package com.usian.controller;

import com.alibaba.fastjson.JSONObject;
import com.usian.mapper.CouMapper;
import com.usian.mapper.StuMapper;
import com.usian.pojo.Course;
import com.usian.pojo.Stu;
import com.usian.service.StuService;
import com.usian.utils.WriterUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

@RestController
@RequestMapping("/xx")
public class StuController {

    @Autowired
    private StuMapper StuMapper;
    @Autowired
    private CouMapper couMapper;


    //导入
    @RequestMapping("/save")
    private void savebatch(MultipartFile file, HttpServletRequest request, HttpServletResponse response) {
        JSONObject result = new JSONObject();
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            Sheet sheet1 = workbook.getSheetAt(0);
            int lastRowNum = sheet1.getLastRowNum();
            List<Stu> list = new ArrayList<>();
            for (int i = 1; i <= lastRowNum; i++) {
                Row row = sheet1.getRow(i);
                String name = row.getCell(0).getStringCellValue();
                int age = (int) row.getCell(1).getNumericCellValue();
                String sex = row.getCell(2).getStringCellValue();

                Stu stu = new Stu();
                stu.setName(name);
                stu.setAge(age);
                stu.setSex(sex);

                list.add(stu);
            }
            StuMapper.savebatch(list);

            List<Course> list2 = new ArrayList<>();
            HSSFSheet sheet2 = workbook.getSheetAt(1);
            int lastRowNum2 = sheet2.getLastRowNum();
            for (int i = 1; i <= lastRowNum2; i++) {
                Row row = sheet2.getRow(i);
                String name = row.getCell(0).getStringCellValue();
                Course course = new Course();
                course.setName(name);
                list2.add(course);
            }
            couMapper.sevebatch(list2);
            result.put("success", true);
        } catch (IOException e) {
            e.printStackTrace();
            result.put("errorMsg", "对不起，导入失败");
        }

        WriterUtil.write(response, result.toString());
    }


    @RequestMapping("/export")
    public void export(HttpServletResponse response, HttpServletRequest request) {
        JSONObject result = new JSONObject();
        try {
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet();
            XSSFRow row = sheet.createRow(0);
            String[] str = {"姓名", "年龄", "性别"};
            for (int i = 0; i < str.length; i++) {
                XSSFCell cell = row.createCell(i);
                cell.setCellValue(str[i]);
            }
            List<Stu> studentList = StuMapper.selectList(null);
            for (int i = 0; i < studentList.size(); i++) {
                Stu student = studentList.get(i);
                XSSFRow row1 = sheet.createRow(i + 1);
                row1.createCell(0).setCellValue(student.getName());
                row1.createCell(1).setCellValue(student.getAge());
                row1.createCell(2).setCellValue(student.getSex());
            }
            /*创建第二个sheet页*/
            XSSFSheet sheet2 = workbook.createSheet();
            XSSFRow row2 = sheet2.createRow(0);
            String[] str2 = {"课程"};
            for (int i = 0; i < str2.length; i++) {
                XSSFCell cell = row2.createCell(i);
                cell.setCellValue(str2[i]);
            }
            List<Course> courseList = couMapper.selectList(null);
            for (int i = 0; i < courseList.size(); i++) {
                Course course = courseList.get(i);

                XSSFRow row1 = sheet2.createRow(i + 1);
                row1.createCell(0).setCellValue(course.getName());

            }
            /*写出文件*/
            OutputStream os;
            /*创建文件*/                  /*文件路径*/
            File file = new File("E:\\哈哈哈.xlsx");
            /*不存在*/
            if (!file.exists()) {
                /*就自动创建*/
                file.createNewFile();
            }
            /*文件输出流*/
            os = new FileOutputStream(file);
            workbook.write(os);
            os.close();

            result.put("success", true);
        } catch (Exception e) {
            e.printStackTrace();
        }
        WriterUtil.write(response, result.toString());
    }
}
